package DAO;

import javabean.Complaint;

import java.sql.*;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ComplaintDAO implements IComplaintDAO {
    @Override
    public List<Complaint> getAllComplaint() throws SQLException {
        List<Complaint> complaintList = new ArrayList<>();

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = ConnectManager.getConnection();
            stmt = conn.createStatement();
            String sql = "SELECT * FROM Complaint";
            rs = stmt.executeQuery(sql);

            while (rs.next()) {
                Complaint complaint = new Complaint();
                complaint.setComplaintID(rs.getInt("ComplaintID"));
                complaint.setOrderID(rs.getInt("OrderID"));
                complaint.setStaffID(rs.getString("StaffID"));
                complaint.setCustomerID(rs.getString("CustomerID"));
                complaint.setEvaluation(rs.getString("Evaluation"));
                complaint.setComplaintStatus(rs.getString("ComplaintStatus"));
                complaint.setComplaintDate(rs.getTimestamp("ComplaintDate"));
                complaint.setComplaintDescription(rs.getString("ComplaintDescription"));
                complaint.setHandlingResult(rs.getString("HandlingResult"));

                complaintList.add(complaint);
            }
        } finally {
            ConnectManager.closeConnection(conn, stmt, rs);
        }

        return complaintList;
    }


    @Override
    public List<Complaint> getAllUnassignedComplaint() throws SQLException {
        List<Complaint> complaintList = new ArrayList<>();

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = ConnectManager.getConnection();
            stmt = conn.createStatement();
            String sql = "SELECT * FROM UnassignedComplaint";
            rs = stmt.executeQuery(sql);

            while (rs.next()) {
                Complaint complaint = new Complaint();
                complaint.setComplaintID(rs.getInt("ComplaintID"));
                complaint.setOrderID(rs.getInt("OrderID"));
                complaint.setCustomerID(rs.getString("CustomerID"));
                complaint.setComplaintDate(rs.getTimestamp("ComplaintDate"));
                complaint.setComplaintDescription(rs.getString("ComplaintDescription"));
                complaintList.add(complaint);
            }
        } finally {
            ConnectManager.closeConnection(conn, stmt, rs);
        }

        return complaintList;
    }


    @Override
    public List<Complaint> getAllComplaintByCustomerID(String customerID) throws SQLException {
        List<Complaint> complaintList = new ArrayList<>();

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = ConnectManager.getConnection();
            String sql = "SELECT * FROM Complaint WHERE CustomerID = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, customerID);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Complaint complaint = new Complaint();
                complaint.setComplaintID(rs.getInt("ComplaintID"));
                complaint.setOrderID(rs.getInt("OrderID"));
                complaint.setStaffID(rs.getString("StaffID"));
                complaint.setCustomerID(rs.getString("CustomerID"));
                complaint.setEvaluation(rs.getString("Evaluation"));
                complaint.setComplaintStatus(rs.getString("ComplaintStatus"));
                complaint.setComplaintDate(rs.getTimestamp("ComplaintDate"));
                complaint.setComplaintDescription(rs.getString("ComplaintDescription"));
                complaint.setHandlingResult(rs.getString("HandlingResult"));

                complaintList.add(complaint);
            }
        } finally {
            ConnectManager.closeConnection(conn, pstmt, rs);
        }

        return complaintList;
    }


    @Override
    public List<Complaint> getAllUnassignedComplaintByCustomerID(String customerID) throws SQLException {
        List<Complaint> complaintList = new ArrayList<>();

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = ConnectManager.getConnection();
            String sql = "SELECT * FROM UnassignedComplaint WHERE CustomerID = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, customerID);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Complaint complaint = new Complaint();
                complaint.setComplaintID(rs.getInt("ComplaintID"));
                complaint.setOrderID(rs.getInt("OrderID"));
                complaint.setCustomerID(rs.getString("CustomerID"));
                complaint.setComplaintDate(rs.getTimestamp("ComplaintDate"));
                complaint.setComplaintDescription(rs.getString("ComplaintDescription"));
                complaintList.add(complaint);
            }
        } finally {
            ConnectManager.closeConnection(conn, pstmt, rs);
        }

        return complaintList;
    }


    @Override
    public List<Complaint> getAllComplaintByStaffID(String staffID) throws SQLException {
        List<Complaint> complaintList = new ArrayList<>();

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = ConnectManager.getConnection();
            String sql = "SELECT * FROM Complaint WHERE StaffID = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, staffID);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Complaint complaint = new Complaint();
                complaint.setComplaintID(rs.getInt("ComplaintID"));
                complaint.setOrderID(rs.getInt("OrderID"));
                complaint.setStaffID(rs.getString("StaffID"));
                complaint.setCustomerID(rs.getString("CustomerID"));
                complaint.setEvaluation(rs.getString("Evaluation"));
                complaint.setComplaintStatus(rs.getString("ComplaintStatus"));
                complaint.setComplaintDate(rs.getTimestamp("ComplaintDate"));
                complaint.setComplaintDescription(rs.getString("ComplaintDescription"));
                complaint.setHandlingResult(rs.getString("HandlingResult"));

                complaintList.add(complaint);
            }
        } finally {
            ConnectManager.closeConnection(conn, pstmt, rs);
        }

        return complaintList;
    }

    @Override
    public boolean addUnassignedComplaint(Complaint complaint) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = ConnectManager.getConnection();
            String sql = "INSERT INTO UnassignedComplaint (OrderID, CustomerID, ComplaintDate, ComplaintDescription) VALUES (?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, complaint.getOrderID());
            pstmt.setString(2, complaint.getCustomerID());
            //当前时间 TODO 不够优雅
            pstmt.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
            pstmt.setString(4, complaint.getComplaintDescription());

            int rowsAffected = pstmt.executeUpdate();
            return rowsAffected > 0;
        } finally {
            ConnectManager.closeConnection(conn, pstmt, null);
        }
    }

    @Override
    public boolean addComplaint(int ComplaintID, String StaffID) throws SQLException {
        Connection conn = null;
        CallableStatement cstmt = null;
        try {
            conn = ConnectManager.getConnection();
            String sql = "{CALL AssignComplaint(?, ?)}";
            cstmt = conn.prepareCall(sql);
            cstmt.setInt(1, ComplaintID);
            cstmt.setString(2, StaffID);

            cstmt.execute();
            return true;
        } finally {
            ConnectManager.closeConnection(conn, cstmt, null);
        }
    }


    @Override
    public boolean deleteComplaint(int complaintID) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = ConnectManager.getConnection();
            String sql = "DELETE FROM Complaint WHERE ComplaintID = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, complaintID);
            int rowsAffected = pstmt.executeUpdate();
            return rowsAffected > 0;
        } finally {
            ConnectManager.closeConnection(conn, pstmt, null);
        }
    }


    @Override
    public boolean updateComplaint(Complaint complaint) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = ConnectManager.getConnection();
            String sql = "UPDATE Complaint SET OrderID = ?, StaffID = ?, ComplaintDescription = ?, ComplaintStatus = ? WHERE ComplaintID = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, complaint.getOrderID());
            pstmt.setString(2, complaint.getStaffID());
            pstmt.setString(3, complaint.getComplaintDescription());
            pstmt.setString(4, complaint.getComplaintStatus());
            pstmt.setInt(5, complaint.getComplaintID());

            int rowsAffected = pstmt.executeUpdate();
            return rowsAffected > 0;
        } finally {
            ConnectManager.closeConnection(conn, pstmt, null);
        }
    }


    @Override
    public Complaint getComplaintByID(int complaintID) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = ConnectManager.getConnection();
            String sql = "SELECT * FROM Complaint WHERE ComplaintID = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, complaintID);

            rs = pstmt.executeQuery();
            if (rs.next()) {
                Complaint complaint = new Complaint();
                complaint.setComplaintID(rs.getInt("ComplaintID"));
                complaint.setOrderID(rs.getInt("OrderID"));
                complaint.setStaffID(rs.getString("StaffID"));
                complaint.setCustomerID(rs.getString("CustomerID"));
                complaint.setEvaluation(rs.getString("Evaluation"));
                complaint.setComplaintStatus(rs.getString("ComplaintStatus"));
                complaint.setComplaintDate(rs.getTimestamp("ComplaintDate"));
                complaint.setComplaintDescription(rs.getString("ComplaintDescription"));
                complaint.setHandlingResult(rs.getString("HandlingResult"));

                return complaint;
            }

            return null;
        } finally {
            ConnectManager.closeConnection(conn, pstmt, rs);
        }
    }


    @Override
    public Complaint getComplaintByOrderID(int orderID) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = ConnectManager.getConnection();
            String sql = "SELECT * FROM Complaint WHERE OrderID = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, orderID);

            rs = pstmt.executeQuery();
            if (rs.next()) {
                Complaint complaint = new Complaint();
                complaint.setComplaintID(rs.getInt("ComplaintID"));
                complaint.setOrderID(rs.getInt("OrderID"));
                complaint.setStaffID(rs.getString("StaffID"));
                complaint.setCustomerID(rs.getString("CustomerID"));
                complaint.setEvaluation(rs.getString("Evaluation"));
                complaint.setComplaintStatus(rs.getString("ComplaintStatus"));
                complaint.setComplaintDate(rs.getTimestamp("ComplaintDate"));
                complaint.setComplaintDescription(rs.getString("ComplaintDescription"));
                complaint.setHandlingResult(rs.getString("HandlingResult"));

                return complaint;
            }

            return null;
        } finally {
            ConnectManager.closeConnection(conn, pstmt, rs);
        }
    }
    @Override
    public boolean evaluateComplaint(int ComplaintID, String Evaluation) throws SQLException{
        Connection conn = null;
        PreparedStatement pstmt = null;
        try{
            conn = ConnectManager.getConnection();
            String sql = "UPDATE Complaint SET Evaluation = ? WHERE ComplaintID = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, Evaluation);
            pstmt.setInt(2, ComplaintID);
            int rowsAffected = pstmt.executeUpdate();
            return rowsAffected > 0;
        }finally{
            ConnectManager.closeConnection(conn, pstmt, null);
        }
    }

}
